"""
cron: */30 * * * *
new Env('短视频数据分析专业版');
"""
import requests
import os
import mysql.connector
import json
import time
from datetime import datetime
from bs4 import BeautifulSoup

class VideoAnalysisPro:
    def __init__(self):
        self.connection = mysql.connector.connect(
            host=os.environ.get('HOST'),
            user=os.environ.get('USER'),
            password=os.environ.get('PASSWORD'),
            database=os.environ.get('DATABASE')
        )
        self.cursor = self.connection.cursor()
        self.create_tables()
        
    def create_tables(self):
        """创建必要的数据表"""
        # 热门视频表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_hot_videos (
                id INT AUTO_INCREMENT PRIMARY KEY,
                platform VARCHAR(20),
                video_id VARCHAR(100),
                title VARCHAR(500),
                author VARCHAR(100),
                play_count INT,
                like_count INT,
                comment_count INT,
                share_count INT,
                duration INT,
                tags TEXT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_platform (platform),
                INDEX idx_create_time (create_time)
            )
        """)
        
        # 热门话题表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_hot_topics (
                id INT AUTO_INCREMENT PRIMARY KEY,
                platform VARCHAR(20),
                topic_name VARCHAR(200),
                video_count INT,
                view_count BIGINT,
                hot_value DECIMAL(10,2),
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_platform (platform),
                INDEX idx_hot_value (hot_value)
            )
        """)
        
        # 创作者分析表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_creator_analysis (
                id INT AUTO_INCREMENT PRIMARY KEY,
                platform VARCHAR(20),
                author_id VARCHAR(100),
                author_name VARCHAR(100),
                follower_count INT,
                total_likes BIGINT,
                avg_play_count INT,
                video_count INT,
                best_publish_time VARCHAR(20),
                best_video_duration INT,
                top_tags TEXT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_platform (platform),
                INDEX idx_author_id (author_id)
            )
        """)

    def get_douyin_hot_videos(self):
        """获取抖音热门视频数据"""
        try:
            # 使用移动端API
            headers = {
                'Accept': 'application/json',
                'User-Agent': 'Mozilla/5.0 (iPhone; CPU iPhone OS 14_7_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 BytedanceWebview',
                'Accept-Language': 'zh-CN,zh;q=0.9',
                'X-SS-REQ-TICKET': str(int(time.time() * 1000)),
                'sdk-version': '2',
                'X-Khronos': str(int(time.time())),
            }
            
            # 使用移动端热门视频API
            api_url = 'https://api.amemv.com/aweme/v1/hot/search/list/'
            params = {
                'device_platform': 'iphone',
                'aid': '1128',
                'app_name': 'aweme',
                'device_type': 'iPhone13,2',
                'version_code': '17.4.0',
                'count': '20'
            }
            
            response = requests.get(api_url, headers=headers, params=params)
            
            if response.status_code == 200:
                data = response.json()
                videos = data.get('aweme_list', [])
                
                for video in videos:
                    video_data = {
                        'platform': 'douyin',
                        'video_id': video.get('aweme_id'),
                        'title': video.get('desc'),
                        'author': video.get('author', {}).get('nickname'),
                        'play_count': video.get('statistics', {}).get('play_count'),
                        'like_count': video.get('statistics', {}).get('digg_count'),
                        'comment_count': video.get('statistics', {}).get('comment_count'),
                        'share_count': video.get('statistics', {}).get('share_count'),
                        'duration': video.get('duration'),
                        'tags': json.dumps([tag.get('hashtag_name') for tag in video.get('text_extra', [])])
                    }
                    
                    self.save_video_data(video_data)
                    
        except Exception as e:
            print(f"获取抖音热门视频失败: {str(e)}")

    def get_kuaishou_hot_videos(self):
        """获取快手热门视频数据"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (iPhone; CPU iPhone OS 14_7_1 like Mac OS X) AppleWebKit/605.1.15',
                'Cookie': os.environ.get('KUAISHOU_COOKIE', '')
            }
            
            url = 'https://www.kuaishou.com/graphql'
            response = requests.post(url, headers=headers)
            
            if response.status_code == 200:
                data = response.json()
                videos = data.get('data', {}).get('visionHotRank', {}).get('feeds', [])
                
                for video in videos:
                    video_data = {
                        'platform': 'kuaishou',
                        'video_id': video.get('photoId'),
                        'title': video.get('caption'),
                        'author': video.get('author', {}).get('name'),
                        'play_count': video.get('viewCount'),
                        'like_count': video.get('likeCount'),
                        'comment_count': video.get('commentCount'),
                        'share_count': video.get('shareCount'),
                        'duration': video.get('duration'),
                        'tags': json.dumps(video.get('tags', []))
                    }
                    
                    self.save_video_data(video_data)
                    
        except Exception as e:
            print(f"获取快手热门视频失败: {str(e)}")

    def analyze_hot_topics(self):
        """分析热门话题"""
        try:
            query = """
                SELECT tags, play_count, platform
                FROM ningm_hot_videos
                WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
            """
            self.cursor.execute(query)
            videos = self.cursor.fetchall()
            
            topic_stats = {}
            for tags, play_count, platform in videos:
                tags_list = json.loads(tags)
                for tag in tags_list:
                    if tag not in topic_stats:
                        topic_stats[tag] = {
                            'video_count': 0,
                            'view_count': 0,
                            'platform': platform
                        }
                    topic_stats[tag]['video_count'] += 1
                    topic_stats[tag]['view_count'] += play_count
            
            # 保存话题统计数据
            for topic, stats in topic_stats.items():
                hot_value = (stats['view_count'] / 10000) * (stats['video_count'] ** 0.5)
                
                insert_query = """
                    INSERT INTO ningm_hot_topics 
                    (platform, topic_name, video_count, view_count, hot_value) 
                    VALUES (%s, %s, %s, %s, %s)
                """
                insert_values = (
                    stats['platform'],
                    topic,
                    stats['video_count'],
                    stats['view_count'],
                    hot_value
                )
                self.cursor.execute(insert_query, insert_values)
                
            self.connection.commit()
            
        except Exception as e:
            print(f"分析热门话题失败: {str(e)}")
            self.connection.rollback()

    def analyze_creator_performance(self):
        """分析创作者表现"""
        try:
            query = """
                SELECT author, platform, COUNT(*) as video_count,
                       AVG(play_count) as avg_play_count,
                       SUM(like_count) as total_likes,
                       AVG(duration) as avg_duration
                FROM ningm_hot_videos
                WHERE create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)
                GROUP BY author, platform
                HAVING video_count >= 3
            """
            self.cursor.execute(query)
            creators = self.cursor.fetchall()
            
            for creator in creators:
                author, platform, video_count, avg_play_count, total_likes, avg_duration = creator
                
                # 获取创作者最佳发布时间
                time_query = """
                    SELECT HOUR(create_time) as hour,
                           AVG(play_count) as avg_plays
                    FROM ningm_hot_videos
                    WHERE author = %s AND platform = %s
                    GROUP BY HOUR(create_time)
                    ORDER BY avg_plays DESC
                    LIMIT 1
                """
                self.cursor.execute(time_query, (author, platform))
                best_time = self.cursor.fetchone()
                
                insert_query = """
                    INSERT INTO ningm_creator_analysis 
                    (platform, author_name, video_count, avg_play_count,
                     total_likes, best_publish_time, best_video_duration) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    platform,
                    author,
                    video_count,
                    int(avg_play_count),
                    total_likes,
                    f"{best_time[0]:02d}:00" if best_time else "00:00",
                    int(avg_duration) if avg_duration else 0
                )
                self.cursor.execute(insert_query, insert_values)
                
            self.connection.commit()
            
        except Exception as e:
            print(f"分析创作者表现失败: {str(e)}")
            self.connection.rollback()

    def save_video_data(self, video_data):
        """保存视频数据"""
        try:
            insert_query = """
                INSERT INTO ningm_hot_videos 
                (platform, video_id, title, author, play_count, 
                 like_count, comment_count, share_count, duration, tags) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                video_data['platform'],
                video_data['video_id'],
                video_data['title'],
                video_data['author'],
                video_data['play_count'],
                video_data['like_count'],
                video_data['comment_count'],
                video_data['share_count'],
                video_data['duration'],
                video_data['tags']
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"保存视频数据失败: {str(e)}")
            self.connection.rollback()

    def run(self):
        """运行分析系统"""
        try:
            # 1. 获取各平台热门视频数据
            self.get_douyin_hot_videos()
            self.get_kuaishou_hot_videos()
            
            # 2. 分析热门话题
            self.analyze_hot_topics()
            
            # 3. 分析创作者表现
            self.analyze_creator_performance()
            
        except Exception as e:
            print(f"系统运行失败: {str(e)}")
        finally:
            self.cursor.close()
            self.connection.close()

if __name__ == "__main__":
    analyzer = VideoAnalysisPro()
    analyzer.run() 